from sqlalchemy import create_engine

HOSTNAME = '172.16.61.221'
PORT = '3306'
DATABASE = 'mydb'
USERNAME ='jhd'
PASSWORD = 'jihui@duo'

#engine=create_engine('mysql+pymysql://root:qwe123@localhost:3306/test',echo=True)

DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)
print(DB_URI)

engine = create_engine(DB_URI)
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base(engine)

from sqlalchemy import Column,Integer,String

class User(Base):
 	__tablename__ = 'user'
 	id = Column(Integer,primary_key=True,autoincrement=True)
 	username = Column(String(50))
 	password = Column(String(100))

 	def __repr__(self):
 		return '<User(id="%s",username="%s",passwd="%s")>' % (self.id,self.username,self.password)

#将创建好的user类，映射到数据库的user表中
Base.metadata.create_all()

# 需要定义个会话对象
# sessionmaker初始化一个类对象
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
session = Session()
def add_user():
	user1 = User(password='99999')
	session.add(user1)
	session.add_all([
		User(username='bbbb',password='22222'),
		User(username='cccc',password='1111'),
		User(username='dddd',password='1111')])
	session.commit()

#add_user()


def update_user():
	user = session.query(User).filter_by(id=3)[0]
	print(user)
	user.username = 'kakakakak88'
	session.commit()


#update_user()
def delete_user():
    user = session.query(User).filter_by(username='kakakakak88').first()
    print(user)
    session.delete(user)
    session.commit()

#delete_user()

def search_user():
	#rs = session.query(User).all()
	#rs = session.query(User).first()
	#rs = session.query(User).filter_by(username='aaaa').all()
	#rs = session.query(User).get(2)
	#rs = session.query(User).filter(User.username=='taka').all()
    rs = session.query(User).filter(User.id==3).all()[0]
    print(rs,type(rs))

#search_user()
#rs = session.query(User).filter(User.username=='aaaa').all()
#rs = session.query(User).filter(User.username.like('%a%')).all()
#rs = session.query(User).filter(~User.password.in_(['1111','22222'])).all()
#rs = session.query(User).filter(User.password.notin_(['1111','22222'])).all()
#rs = session.query(User).filter(User.username==None).all()
#rs = session.query(User).filter(User.username.is_(None)).all()
# rs = session.query(User).filter(User.username=='None',User.password=='99999')
# print(rs,type(rs))

from sqlalchemy import or_
rs = session.query(User).filter(or_(User.username=='aaaa',User.username=='dddd')).all()
print(rs)














